//
//  KKDataBaseManager.m
//  FreightAssistant
//
//  Created by leo on 15/11/9.
//  Copyright © 2015年 leo. All rights reserved.
//

#import "KKDataBaseManager.h"

#import "sqlite3.h"

static NSString *KKDataBaseFileName = @"mydatabase.sqlite";

@interface KKDataBaseManager() {
    
    NSString *_dbFilePath;
    
    sqlite3 *_db;
    
    KKDataBaseManagerFinish _finishHandle;
}

@end

@implementation KKDataBaseManager

- (instancetype)init {
    self = [super init];
    if (self) {
        
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *documentsDirectory = [paths objectAtIndex:0];
        _dbFilePath = [documentsDirectory stringByAppendingPathComponent: KKDataBaseFileName];
        
        NSFileManager *fileManager = [NSFileManager defaultManager];
        if (![fileManager fileExistsAtPath: _dbFilePath]) {
            
            NSString *dbFilePath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent: KKDataBaseFileName];
            [fileManager copyItemAtPath: dbFilePath toPath: _dbFilePath error: nil];
        }
        
//        [self openDB];
        
        //  双表查询
        //            select KK_SupplyOrder.* ,KK_SupplyOrder_Logistics.* from KK_SupplyOrder left join KK_SupplyOrder_Logistics on KK_SupplyOrder.identifier=KK_SupplyOrder_Logistics.orderId
        
        //  三表查询
        //            select  name, price
        //            from TestA a
        //            inner join TestC c on a.id = c.aid
        //            inner join TestB b on b.id = c.bid
    }
    return self;
}

+ (instancetype)defaultManager {
    
    static KKDataBaseManager *manager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [KKDataBaseManager new];
    });
    return manager;
}

- (NSString*)private_getIdValue {
    
    NSString* idValue = [NSString stringWithFormat: @"%.0f",[NSDate timeIntervalSinceReferenceDate] * 100000];
    
    NSLog(@"idValue = %@", idValue);
    idValue = [idValue kk_md5String];
    return idValue;
}

#pragma mark -
#pragma mark - Private methods

- (void)openDB {
    
    if (sqlite3_open([_dbFilePath UTF8String], &_db) != SQLITE_OK) {
        sqlite3_close(_db);
        
        NSLog(@"数据库打开失败");
    }
}

- (void)executeSQLString:(NSString*)sqlString withResult:(id)result {
    if (sqlString.length == 0) {
        
        _finishHandle(result, false);
        return;
    }
    
    [self openDB];
    
    char *error = NULL;
    if (sqlite3_exec(_db, [sqlString UTF8String], NULL, NULL, &error) != SQLITE_OK) {
        sqlite3_close(_db);
        
        if (_finishHandle) {
            _finishHandle(result,false);
        }
    }
    else {
        if (_finishHandle) {
            _finishHandle(result,true);
        }
        
        sqlite3_close(_db);
    }
}

- (void)executeSQLString:(NSString*)sqlString {
    [self executeSQLString: sqlString withResult: nil];
}

#pragma mark - Public methods

- (void)insertRecordIntoTableName:(NSString*)tableName withRecords:(NSDictionary*)records finishHandle:(KKDataBaseManagerFinish)handle{
    
    if (tableName == nil || records == nil) {
        
        if (handle) {
            handle(nil,false);
        }
        return;
    }
    
    NSMutableString *keyString = [NSMutableString stringWithString: @"'id'"];
    NSString *idValue = [self private_getIdValue];
    NSMutableString *valueString = [NSMutableString stringWithFormat: @"'%@'",idValue];
    NSArray *allKeys = [records allKeys];
    
    for (NSString *key in allKeys) {
        
        NSString *value = records[key];
        [keyString appendFormat: @",'%@'", key];
        [valueString appendFormat: @",'%@'", value];
    }
    
    _finishHandle = handle;
    NSString *sqlString = [NSString stringWithFormat:@"insert into '%@' (%@) values (%@)", tableName, keyString, valueString];
    [self executeSQLString: sqlString withResult: idValue];
}

- (void)searchRecordWithTableName:(NSString*)tableName withCondition:(NSDictionary*)condition finishHandle:(KKDataBaseManagerFinish)handle{
    
    if (tableName == nil) {
        if (handle) {
            handle(nil,false);
        }
        return;
    }
    [self openDB];
    //  获取数据库中表的字段
    NSMutableDictionary *fieldDictionary = [NSMutableDictionary dictionary];
    NSString* searchSQL = [NSString stringWithFormat: @"pragma table_info ('%@');", tableName];
    sqlite3_stmt *fieldStatement = nil;
    if (sqlite3_prepare(_db, [searchSQL UTF8String], -1, &fieldStatement, nil) == SQLITE_OK) {
        
        NSInteger index = 0;
        while (sqlite3_step(fieldStatement) == SQLITE_ROW) {
            
            char *type_title = (char *)sqlite3_column_text(fieldStatement, 1);
            NSString *typeName = [[NSString alloc] initWithUTF8String: type_title];
            [fieldDictionary setValue: typeName forKey: [NSString stringWithFormat: @"%@", @(index)]];
            
            index++;
        }
        sqlite3_finalize(fieldStatement);
    }
    else {
        return;
    }
    
    //  获取数据库中的数据
    NSString *sqlString = nil;
    if (condition.count == 0) {
        sqlString = [NSString stringWithFormat: @"select * from '%@' order by rowid desc ", tableName];
    }
    else {
        NSMutableString *conditionString = [NSMutableString string];
        {
            NSArray *allKeys = [condition allKeys];
            for (NSString *key in allKeys) {
                NSString *value = condition[key];
                
                if (conditionString.length == 0) {
                    [conditionString appendFormat: @"%@='%@'", key, value];
                }
                else {
                    [conditionString appendFormat: @"and %@='%@'", key, value];
                }
            }
        }
        
        sqlString = [NSString stringWithFormat: @"select * from '%@' where %@ order by rowid desc", tableName, conditionString];
    }
    
    sqlite3_stmt *statement = nil;
    if (sqlite3_prepare(_db, [sqlString UTF8String], -1, &statement, nil) == SQLITE_OK) {
        
        NSMutableArray *itemArray = [NSMutableArray array];
        NSArray *allKeys = [fieldDictionary allKeys];
        
        while (sqlite3_step(statement) == SQLITE_ROW) {
            
            NSMutableDictionary *itemDictionary = [NSMutableDictionary dictionary];
            for (NSString *key in allKeys) {
                
                NSString *fieldValue = fieldDictionary[key];
                
                char *value = (char *)sqlite3_column_text(statement, [key intValue]);
                
                if (value != NULL) {
                    NSString *itemValue = [[NSString alloc] initWithUTF8String: value];
                    [itemDictionary setValue: itemValue forKey: fieldValue];
                }
            }
            [itemArray addObject: itemDictionary];
        }
        sqlite3_finalize(statement);
        
        if (handle) {
            handle(itemArray, true);
        }
    }
    else {
        if (handle) {
            handle(nil, false);
        }
    }
    
    sqlite3_close(_db);
}

- (void)updateRecordWithTableName:(NSString*)tableName withRecords:(NSDictionary*)records withCondition:(NSDictionary*)condition finishHandle:(KKDataBaseManagerFinish)handle {
    
    if (tableName == nil || records == nil || condition == nil) {
        
        if (handle) {
            handle(nil,false);
        }
        return;
    }
    
    NSMutableString *updateValueString = [NSMutableString string];
    
    NSArray *allKeys = [records allKeys];
    
    for (NSString *key in allKeys) {
        
        NSString *value = records[key];
        if (updateValueString.length == 0) {
            [updateValueString appendFormat: @"'%@'='%@'", key, value];
        }
        else {
            [updateValueString appendFormat: @",'%@'='%@'", key, value];
        }
    }
    
    NSString *identifier = condition[@"identifier"];
    
    NSMutableString *conditionString = [NSMutableString string];
    {
        NSString *key = [[condition allKeys] firstObject];
        NSString *value = condition[key];
        [conditionString appendFormat: @"%@='%@'", key, value];
    }
    
    _finishHandle = handle;
    NSString *sqlString = [NSString stringWithFormat:@"update '%@' set %@ where %@", tableName, updateValueString, conditionString];
    [self executeSQLString: sqlString withResult: identifier];
}

- (void)removeRecordWithTableName:(NSString*)tableName withCondition:(NSDictionary*)condition finishHandle:(KKDataBaseManagerFinish)handle {
    
    if (tableName == nil || condition == nil) {
        
        if (handle) {
            handle(nil,false);
        }
        return;
    }
    
    NSMutableString *conditionString = [NSMutableString string];
    {
        NSString *key = [[condition allKeys] firstObject];
        NSString *value = condition[key];
        [conditionString appendFormat: @"%@='%@'", key, value];
    }
    
    _finishHandle = handle;
    NSString *sqlString = [NSString stringWithFormat:@"delete from '%@' where %@", tableName, conditionString];
    
    [self executeSQLString: sqlString];
}

- (void)executeSQLStringWithTableName:(NSString*)tableName withValueKeys:(NSArray*)keys withCondition:(NSDictionary *)condition finishHandle:(KKDataBaseManagerFinish)handle {
    
    if (keys.count == 0) {
        [self searchRecordWithTableName: tableName withCondition: condition finishHandle: handle];
    }
    else {
        
       [self openDB];
        
        NSMutableString *targetString = [NSMutableString string];
        
        for (NSString *key in keys) {
            
            if (targetString.length) {
                [targetString appendFormat: @",%@", key];
            }
            else {
                [targetString appendString: key];
            }
        }
        
        NSMutableString *conditionString = [NSMutableString string];
        
        [condition enumerateKeysAndObjectsUsingBlock:^(NSString *key, NSString *obj, BOOL * _Nonnull stop) {
            
            if (conditionString.length) {
                [conditionString appendFormat: @"and %@='%@'", key, obj];
            }
            else {
                [conditionString appendFormat: @"%@='%@'", key, obj];
            }
            
        }];
        
        NSString *sqlString = nil;
        if (conditionString.length) {
            sqlString = [NSString stringWithFormat: @"select %@ from '%@' where %@ order by rowid desc", targetString ,tableName, conditionString];
        }
        else {
            sqlString = [NSString stringWithFormat: @"select %@ from '%@' order by rowid desc", targetString ,tableName];
        }
        
        sqlite3_stmt *statement = nil;
        
        int dbResult = sqlite3_prepare(_db, [sqlString UTF8String], -1, &statement, nil);
        
        if (dbResult == SQLITE_OK) {
            
            NSMutableArray *itemArray = [NSMutableArray array];
            
            while (sqlite3_step(statement) == SQLITE_ROW) {
                
                NSMutableDictionary *itemDictionary = [NSMutableDictionary dictionary];
                for (int index = 0; index < keys.count; index++) {
                    
                    char *value = (char *)sqlite3_column_text(statement, index);
                    
                    if (value != NULL) {
                        NSString *itemValue = [[NSString alloc] initWithUTF8String: value];
                        [itemDictionary setValue: itemValue forKey: keys[index]];
                    }
                }
                [itemArray addObject: itemDictionary];
            }
            sqlite3_finalize(statement);
            
            if (handle) {
                handle(itemArray, true);
            }
        }
    }
}


- (NSArray *)executeSQLString:(NSString *)sqlString withValueKeys:(NSArray *)keys {
    
    if (sqlString == nil) {
        return nil;
    }
    
    [self openDB];
    
    sqlite3_stmt *statement = nil;
    
    int dbResult = sqlite3_prepare(_db, [sqlString UTF8String], -1, &statement, nil);
    
    if (dbResult == SQLITE_OK) {
        
        NSMutableArray *itemArray = [NSMutableArray array];
        
        while (sqlite3_step(statement) == SQLITE_ROW) {
            
            NSMutableDictionary *itemDictionary = [NSMutableDictionary dictionary];
            for (int index = 0; index < keys.count; index++) {
                
                char *value = (char *)sqlite3_column_text(statement, index);
                
                if (value != NULL) {
                    NSString *itemValue = [[NSString alloc] initWithUTF8String: value];
                    [itemDictionary setValue: itemValue forKey: keys[index]];
                }
            }
            [itemArray addObject: itemDictionary];
        }
        sqlite3_finalize(statement);
        return itemArray;
    }
    return nil;
}

- (void)execInsertTransactionSQLStringArray:(NSArray *)sqlStrings {
    
    if (sqlStrings.count == 0 ) {
        return;
    }
    
    [self openDB];
    
    char *errorMessage;
    
    int dbResult = sqlite3_exec(_db, "BEGIN", NULL, NULL, &errorMessage);
    if (dbResult == SQLITE_OK) {
        
//        NSLog(@"启动事务成功");
        
        sqlite3_free(errorMessage);
        sqlite3_stmt *statement = nil;
        
        for (NSInteger index = 0; index < sqlStrings.count; index++) {
            
            NSString *sqlString = sqlStrings[index];
            
            if (sqlite3_prepare_v2(_db, [sqlString UTF8String], -1, &statement, NULL) == SQLITE_OK) {
                
                if (sqlite3_step(statement) != SQLITE_DONE) {
                    sqlite3_finalize(statement);
                }
            }
            
            if (sqlite3_exec(_db, "COMMIT", NULL, NULL, &errorMessage) == SQLITE_OK) {
                NSLog(@"提交事务成功");
            }
            sqlite3_free(errorMessage);
        }
    }
    else {
        sqlite3_free(errorMessage);
    }
}


- (NSString *)sqlStringOfInsertRecordIntoTableName:(NSString*)tableName withRecords:(NSDictionary*)records sqlIdentifier:(NSString **)identifier {
    
    if (tableName == nil || records == nil) {
        
        return nil;
    }
    
    NSMutableString *keyString = [NSMutableString stringWithString: @"'id'"];
    
    NSString *idValue = [self private_getIdValue];
    if (identifier) {
        *identifier = [NSString stringWithString: idValue];
    }
    NSMutableString *valueString = [NSMutableString stringWithFormat: @"'%@'",idValue];
    
    NSArray *allKeys = [records allKeys];
    
    for (NSString *key in allKeys) {
        
        NSString *value = records[key];
        [keyString appendFormat: @",'%@'", key];
        [valueString appendFormat: @",'%@'", value];
    }
    
    NSString *sqlString = [NSString stringWithFormat:@"insert into '%@' (%@) values (%@)", tableName, keyString, valueString];
    
    return sqlString;
}

@end
